package com.report.conversion.service.impl;

import com.report.conversion.dao.TemplateDaoMapper;
import com.report.conversion.service.TemplateDataService;
import com.report.conversion.utils.AsposeUtil;
import com.report.conversion.utils.DataSourceUtils;
import com.report.conversion.utils.ExcelReportCore;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.File;
import java.sql.*;
import java.util.*;

/**
 * 报表生成
 *
 * @author: kevin wu
 * @date: 2023年03月14日 17:04
 */
@Service
public class TemplateDataServiceImpl implements TemplateDataService {

    @Autowired
    TemplateDaoMapper templateDaoMapper;
    @Autowired
    DataSourceUtils dataSourceUtils;
    @Autowired
    ExcelReportCore excelReportCore;
    @Autowired
    AsposeUtil asposeUtil;

    /***
     * @description 生成Excel报表并返回路径
     * @param paraMap
     *           templateCode 模板编号
     *           outFileName 生成文件全路径包含文件名
     *           params 报表需要参数 HashMap
     * @return void
     * @author kevin wu
     * @date 2023/3/15 9:19
     */
    @Override
    public String generExcel(HashMap paraMap){

        try {
            HashMap hashMap = prepareData(paraMap);
            String  s = excelReportCore.buildByTemplate(hashMap.get("sourceFileName").toString(), hashMap.get("outFileName").toString(), hashMap);
            return s;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /***
     * @description 生成Pdf报表并返回路径
     * @param paraMap
     *           templateCode 模板编号
     *           outFileName 生成文件全路径包含文件名
     *           params 报表需要参数 HashMap
     * @return void
     * @author kevin wu
     * @date 2023/3/15 9:19
     */
    @Override
    public String generPdf(HashMap paraMap){
        try {

            HashMap hashMap = prepareData(paraMap);//准备数据
            String sourceFileName = hashMap.get("sourceFileName").toString();//模板文件
            String newFileName = hashMap.get("outFileName").toString();//目标文件
            String extName = sourceFileName.substring(sourceFileName.lastIndexOf(".") + 1);
            String outFileName = newFileName.substring(0, newFileName.lastIndexOf(".") + 1)+extName;
            String newPath = newFileName.substring(0, newFileName.lastIndexOf(File.separator) + 1);
            String newName = newFileName.substring(newFileName.lastIndexOf(File.separator) + 1);
            hashMap.put("outFileName",outFileName);
            String  s = excelReportCore.buildByTemplate(hashMap.get("sourceFileName").toString(), hashMap.get("outFileName").toString(), hashMap);

            String s1 = asposeUtil.conversionFile(s, newPath, newName);
            return s1;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /***
     * @description excel报表准备数据
     * @param paraMap
     *           templateCode 模板编号
     *           outFileName 生成文件全路径包含文件名
     *           params 报表需要参数 HashMap
     * @return void
     * @author kevin wu
     * @date 2023/3/15 9:19
     */
    public HashMap prepareData(HashMap paraMap){

        HashMap rtMap = new HashMap();
        rtMap.putAll(paraMap);
        HashMap<String, String> templateByCode = templateDaoMapper.getTemplateByCode(paraMap);
        rtMap.put("sourceFileName",templateByCode.get("tpath"));
        String outFileName = paraMap.get("outFileName").toString();
        List<HashMap> hashMaps = templateDaoMapper.selectTemplateByCode(paraMap);

        for (int i = 0; i < hashMaps.size(); i++) {
            HashMap hashMap = hashMaps.get(i);
            HashMap params = (HashMap)paraMap.get("params");//报表参数
            hashMap.put("params",params);
            List<HashMap> hashMapList = executeSql(hashMap);
            if(!hashMap.get("sjylx").toString().contains("List")){
                HashMap resultMap = hashMapList.get(0);
                rtMap.putAll(resultMap);
            }else{
                rtMap.put(hashMap.get("sjylx").toString(),hashMapList);
            }
        }
        return rtMap;
    }

    /***
     * @description 执行定义的数据源获取结果
     * @param param
     * @return java.util.List<java.util.HashMap>
     * @author kevin wu
     * @date 2023/3/15 15:45
     */
    private List<HashMap> executeSql(HashMap param){

        try {
            String sjyid = param.get("sjyid").toString();//数据源id
            String sjysql = param.get("sjysql").toString();//数据源sql
            HashMap params = (HashMap)param.get("params");//报表参数
            String sql = replaceParams(sjysql, params);
            DataSource source = (DataSource)dataSourceUtils.datasourceMaps.get(sjyid);//获取数据源
            Connection connection = source.getConnection();
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            List list = convertList(rs);
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /***
     * @description 替换sql中的变量组成正式执行SQL
     * @param sql
     * @param params
     * @return java.lang.String
     * @author kevin wu
     * @date 2023/3/15 15:45
     */
    private String replaceParams(String sql,HashMap params){
        Iterator iterator = params.keySet().iterator();
        while(iterator.hasNext()){
            String key = iterator.next().toString();
            String rkey = "{{"+key+"}}";
            while (sql.contains(rkey)){
                sql = sql.replace(rkey,params.get(key).toString());
            }
        }
        return sql;
    }

    /***
     * @description 将结果resultSet 转换成list
     * @param rs
     * @return java.util.List
     * @author kevin wu
     * @date 2023/3/15 15:46
     */
    private List convertList(ResultSet rs) throws SQLException {
        List list = new ArrayList();
        ResultSetMetaData md = rs.getMetaData();//获取键名
        int columnCount = md.getColumnCount();//获取行的数量
        while (rs.next()) {
            Map rowData = new HashMap();//声明Map
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnLabel(i), rs.getObject(i));//获取键名及值
            }
            list.add(rowData);
        }
        return list;
    }

}
